and download
the SQL files and Store Procedures.Click and download
the SQL files and Store Procedures.
Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.
*Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard:
1. DFT-DimBusinessPropertyType
a. Drag and drop a Data Flow
Task, rename it as DFT-DimBusinessPropertyType and double click in it to get
into edit mode
b. Drag and drop OLE DB data
source and double click it to edit. add connection manager and select
the SQL Command and use the query below to provide the required
dataset for property types:
SELECT DISTINCT PropertyType
FROM [MarketDatamart_Staging].[dbo].[SubMarketVsMarket By PropertyTypes]
WHERE PropertyType <> 'NULL'
d. Drag and drop a Derived Column transformation double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.
e. Drag and drop a Data
Conversion transformation double click the Data Conversion transformation
to edit and change the data type of PropertyType to UnicodeString (25)
f. Drag and drop OLE DB Destination and double click it to add connection manager, select the destination table and map the columns.
2. DFT-DimMarket
a. Drag and drop a Data Flow
Task, rename it as DFT - DimMarket and double click in it to get into edit mode
b. Drag and drop OLE DB data
source and double click it to add the source connection manager and select
the SQL Command and use the query below to provide the required dataset
for property types:
SELECT distinct
SubMarketCode, SubMarketName, PropertyType
FROM MarketDatamart_Staging.dbo.QuarterlySupplyDemandModelNational A
Left JOIN MarketDatamart_Staging.[dbo].SubMarketVsMarket By PropertyTypes B
ON A.Submarket = B.SubMarketDisplayName
WHERE SubMarketCode IS NOT NULL
c. Drag and drop Lookup Transformation and double click it to configure. In the general tab in the drop down select the ignore failure option and in connection select the destination connection manager and select DimBusinessPropertyType table and the column lookup for BusinessPropertyKey lookup.
d. Drag and drop Lookup
Transformation. In the general tab in the drop down select the
ignore failure option and in connection select the destination connection
manager and select DimMarket table and the column lookup for MArketKey lookup.
e. Drag and drop a Derived Column transformation and double click it to edit and provide the derived columns as shown in the screenshot.
g. Drag and drop OLE DB Destination and double
click it to add the destination connection manager, select the destination table and
map the column in the mapping tab.
j. Drag and drop a Lookup
transformation and connect the flow from the lookup to lookup the MarketKey column in
the DimMarket Table.
k. Drag and drop OLE DB
Command Transformation and double click it to add the connection manager and on the
component Properties tab in the SQL Command add the below code and then map them to the parameters in the
column mapping .
UPDATE [dbo].[DimMarket]
SET [MarketName] =? , [CBSACode] =?,
[RecordFlag] ='History', [EffectiveDate] =getdate()-1,
[ModifiedDate] = getdate()
WHERE MarketKey = ?
3.
DFT – DimSubmarket
a. Drag and drop a Data Flow Task, rename it as DFT – DimSubmarket and double click in it to get into edit mode
b. Drag and drop OLE DB data source and double click it to edit. Connect to <Your SQL ServerName> dbo. SubMarketVsMarket By PropertyTypes in Data Warehouse area.
c. Click on SQL Command and use the query below to provide the required dataset for property types:
SELECT distinct MarketName, CBSA, PropertyType
FROM [MarketDatamart_Staging].[dbo].[SubMarketVsMarket By PropertyTypes] A
LEFT JOIN [MarketDatamart_Staging].[dbo].[Vw_AllFips] B
ON A.MarketName = B.RCAName
LEFT JOIN [MarketDatamart_Staging].[dbo].[All_Fundamental]C
ON B.RCAName = C.MSA
LEFT JOIN [MarketDatamart_Staging].[dbo].[AllPivot] D
ON C.MSA = D.Metro
WHERE CBSA IS NOT NULL
d. Drag and drop a Sort transformation onto the canvas and drop the precedence constraint from the data source above to it.
e. Double click on one of the Sort Transformation to configure as in below screenshot to sort the source table as per columns you want to sort on in the derived dataset from source table. Indicate the columns you want to output into the destination table as Pass Through.
f. Drag and drop Lookup Transformation onto the canvas and drop precedence constraint from above onto it. Double click it to configure as in screenshot shown to match the DimBusinessPropertyType table to ensure PropertyKey exists.
g. Drag and drop Lookup Transformation onto the canvas and drop the Lookup Match Output precedence constraint from above onto it. Double click to configure as in screenshot shown to match the DimSubmarket table to ensure Marketcode exists.
h. Drag and drop a Derived Column transformation onto the canvas and drop the Lookup No Match Output precedence constraint from above onto it.
i. Double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.
j. Drag and drop a Data Conversion transformation onto the canvas and drop the precedence constraint from above to it.
k. Double click the Data Conversion transformation to edit and change the data type of affected column as shown in the screenshot.
l. Drag and drop OLE DB Destination onto the canvas and drop the precedence constraint from above to it.
m. Double click the OLE DB Destination to edit. Connect to <Your SQL ServerName> dbo.DimSubmarket in the Data Warehouse area using connection manager and map the columns as shown in screenshot to insert new records into the target table.
n. Drag and drop a Lookup transformation onto the canvas and drop the Lookup Match Output precedence constraint from the Lookup –SubmarketKey transformation above onto it. This pipeline will be taken by records which will be modified in the target table.
o. Double click the Lookup transformation to edit and match the DimSubmarket table to ensure SubmarketKey exists as shown in the screenshot.
p. Drag and drop OLE DB Command Transformation onto the canvas for updating. Drop precedence constraint onto it from above. Double click it to configure as below screenshot for updating by providing:
o The connection manager,
o The T-SQL update script in SSIS format with parameters:
UPDATE dbo.DimSubMarket
SET SubMarketName =?,
SubMarketCode =?,
RecordFlag = 'History',
EffectiveDate = getdate() -1,
ModifiedDate = getdate()
WHERE SubMarketKey =?
o Map the parameters to the relevant columns.
4. DFT – DimStates
a. Drag and drop a Data Flow Task, rename it as DFT – DimStates and double click in it to get into edit mode
b. Drag and drop OLE DB data source and double click it to edit. Connect to <Your SQL ServerName> dbo.MarketToStateMapping in the Data Warehouse area.
c. Click on SQL Command and use the query below to provide the required dataset for property types:
SELECT distinct B.[Geography], USStateCode
FROM [MarketDatamart_DW].[dbo].[MarketToStateMapping] A
LEFT JOIN [MarketDatamart_DW].[dbo].[FIPS_Moody] B
ON A.USStateCode = B.[GeoCode]
WHERE [GeoCode] IS NOT NULL
d. Drag and drop a Derived Column transformation onto the canvas and drop the precedence constraint from the data source above to it.
e. Double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.
f. Drag and drop a Data Conversion transformation onto the canvas and drop the precedence constraint from above to it.
g. Double click the Data Conversion transformation to edit and change the data type of affected column as shown in the screenshot.
h. Drag and drop OLE DB Destination onto the canvas and drop the precedence constraint from above to it.
i. Double click the OLE DB Destination to edit. Connect to <Your SQL ServerName> dbo.DimState in the Data Warehouse area using connection manager and map the columns as shown in screenshot.
5. DFT – DimRegion
a. Drag and drop a Data Flow Task, rename it as DFT – DimRegion and double click in it to get into edit mode
b. Drag and drop OLE DB data source and double click it to edit. Connect to <Your SQL ServerName> dbo.RegionSubRegionUSstates in the Data Warehouse area.
c. Click on SQL Command and use the query below to provide the required dataset for property types:
SELECT distinct RegionName
FROM [MarketDatamart_Staging].[dbo].[RegionSubRegionUSstates]
WHERE RegionName <> 'NA'
d. Drag and drop a Derived Column transformation onto the canvas and drop the precedence constraint from the data source above to it.
e. Double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.
f. Drag and drop a Data Conversion transformation onto the canvas and drop the precedence constraint from above to it.
g. Double click the Data Conversion transformation to edit and change the data type of affected column as shown in the screenshot.
h. Drag and drop OLE DB Destination onto the canvas and drop the precedence constraint from above to it.
i. Double click the OLE DB Destination to edit. Connect to <Your SQL ServerName> dbo.DimRegion in the Data Warehouse area using connection manager and map the columns as shown in screenshot.
6. DFT – DimSubRegion
a. Drag and drop a Data Flow Task, rename it as DFT – DimSubRegion and double click in it to get into edit mode
b. Drag and drop OLE DB data source and double click it to edit. Connect to <Your SQL ServerName> dbo.RegionSubRegionUSstates in the Data Warehouse area.
c. Click on SQL Command and use the query below to provide the required dataset for property types:
SELECT distinct SubRegionName
FROM [MarketDatamart_Staging].[dbo].[RegionSubRegionUSstates]
WHERE SubRegionName <> 'NA'
d. Drag and drop a Derived Column transformation onto the canvas and drop the precedence constraint from the data source above to it.
e. Double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.
f. Drag and drop a Data Conversion transformation onto the canvas and drop the precedence constraint from above to it.
g. Double click the Data Conversion transformation to edit and change the data type of affected column as shown in the screenshot.
h. Drag and drop OLE DB Destination onto the canvas and drop the precedence constraint from above to it.
i. Double click the OLE DB Destination to edit. Connect to <Your SQL ServerName> dbo.DimSubRegion in the Data Warehouse area using connection manager and map the columns as shown in screenshot.
7. Execute Store Procedure – DimCalenderDay
a. Drag and drop a Execute SQL Task onto the canvas, rename it as “Execute Store Procedure – DimCalenderDay” and double click in it to get into edit mode.
b. Provide the connection as shown on the General Tab.
c. Then click on Sql Command Tab and provide the T-SQL statement below to invoke stored procedure for creating the DimCalendar table in the Data Warehouse as shown in screenshot:
o execdbo.uspDimCalendarday '01/01/1992', '12/31/2050'
8. DFT- DimPeriodTypeTag
a. Drag and drop a Data Flow Task, rename it as DFT- DimPeriodTypeTag and double click in it to get into edit mode
b. Drag and drop OLE DB data source and double click it to edit. Connect to <Your SQL ServerName> dbo.VW_MarketStatisticSource in the Data Warehouse area.
c. Click on SQL Command and use the query below to provide the required dataset for property types:
SELECT DISTINCT PeriodType from [dbo].[VW_MarketStatisticSource]
d. Drag and drop a Derived Column transformation onto the canvas and drop the precedence constraint from the data source above to it.
e. Double click the Derived Column transformation to edit and provide the derived columns as shown in the screenshot.
f. Drag and drop a Data Conversion transformation onto the canvas and drop the precedence constraint from above to it.
g. Double click the Data Conversion transformation to edit and change the data type of affected column as shown in the screenshot.
h. Drag and drop OLE DB Destination onto the canvas and drop the precedence constraint from above to it.
i. Double click the OLE DB Destination to edit. Connect to <Your SQL ServerName> dbo.DimPeriodTypeTag in the Data Warehouse area using connection manager and map the columns as shown in screenshot.
